﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using DB.SqlHelper;
using Framework.Common.Common;
using Mode;
namespace Framework.DataAccess
{
    public class User
    {
        /// <summary>
        /// 
        /// </summary>
        private readonly DbInstance db;

        public User()
        {
            this.db = DbManager.GetDbInstance();
        }
        /// <summary>
        /// 登陆
        /// </summary>
        /// <param name="ph"></param>
        /// <param name="pw"></param>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public int login(string ph, string pw, out loginInfo userInfo)
        {

            string strSql = "SELECT * FROM MEMBER_VIEW WHERE M_PHONE = '" + ph.Replace("'", "") + "' AND M_PASD = '" + pw.Replace("'", "").ToMD5() + "'";
            DbCommand cmd = db.GetSqlStringCommand(strSql);

            IDataReader reader = db.ExecuteReader(cmd);
            IList<loginInfo> userList = DbManager.FillList<loginInfo>(reader);
            if (!reader.IsClosed)
                reader.Close();
            if (userList.Count > 0)
                userInfo = userList[0];
            else
                userInfo = null;

            return 1;

        }

        /// <summary>
        /// 添加员工
        /// </summary>
        /// <param name="reg"></param>
        /// <returns></returns>
        public int OpUserItem(RegInfo reg)
        {
            //string strSql="if not EXISTS(select id from member_table where phone='{2}') ";
            //       strSql+=" begin  insert member_table(name,sex,phone,department,workcode,email,[password],[date],[status])VALUES('{0}',{1},'{2}','{3}','{4}','{5}','{6}',GETDATE(),0)";
            //       strSql += " SELECT SCOPE_IDENTITY()  end   else   select -99";
            //       strSql = string.Format(strSql, reg.username.Replace("'", ""), reg.sex, reg.tel, reg.department.Replace("'", ""), reg.workcode.Replace("'", ""), reg.email, reg.pwd.ToMD5());

            //DbCommand cmd = db.GetStoredProcCommand("sp_OptionUser");
            //db.AddInParameter(cmd, "@id", DbType.Int32, reg.userid);
            //db.AddInParameter(cmd, "@name", DbType.String, reg.username);
            //db.AddInParameter(cmd, "@sex", DbType.Int32, reg.sex);
            //db.AddInParameter(cmd, "@phone", DbType.String, reg.tel);
            //db.AddInParameter(cmd, "@department", DbType.String, reg.department);
            //db.AddInParameter(cmd, "@workcode", DbType.String, reg.workcode);
            //db.AddInParameter(cmd, "@email", DbType.String, reg.email);
            //db.AddInParameter(cmd, "@password", DbType.String, reg.pwd.ToMD5());
            //db.AddInParameter(cmd, "@areid", DbType.Int32, reg.areid);
            //db.AddInParameter(cmd, "@roleid", DbType.Int32, reg.zwid);
            //db.AddInParameter(cmd, "@status", DbType.Int32, reg.status);

            //db.AddOutParameter(cmd, "@erro", DbType.Int32, 4);
            //db.ExecuteNonQuery(cmd);
            //int err = db.GetParameterValue(cmd, "@erro").ToInt(0);
            //return err;


            int result = 0;

            string sql = "select count(*) from member_table where phone='" + reg.tel + "'";
            int has_reg = db.ExecuteScalarBySql(sql).ToInt();
            if (has_reg > 0)
                return -99;




            StringBuilder sb = new StringBuilder();
            sb.Append("insert member_table(name,sex,phone,department,workcode,email,[password] ,[date],[status] ) ");
            sb.Append("values ('" + reg.username.Replace("'", "") + "',");
            sb.Append(reg.sex + ",'");
            sb.Append(reg.tel + "','");
            sb.Append(reg.department.Replace("'", "") + "','");
            sb.Append(reg.workcode.Replace("'", "") + "','");
            sb.Append(reg.email.Replace("'", "") + "','");
            sb.Append(reg.pwd.ToMD5() + "', GETDATE() , 0 ); Select Scope_Identity()As MID");

            string insert = sb.ToString();

            DbManager.BeginTransaction();
            DbInstance idb = DbManager.GetDbInstance();
            try
            {
                int mid = -1;
                DbCommand cmd = db.GetSqlStringCommand(insert);
                idb.AddInParameter(cmd, "@MID", DbType.Int32, mid);
                object obj = idb.ExecuteScalar(cmd);
                if (null == obj)
                    throw new Exception("插入用户表失败.");

                insert = String.Format("insert user_join values({0},{1})", obj.ToInt(), reg.areid);
                result = idb.ExecuteNonQueryBySql(insert);

                DbManager.Commit();
            }
            catch (Exception ex)
            {
                DbManager.Rollback();
                throw ex;
            }

            return result;

        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="reg"></param>
        /// <returns></returns>
        public int UpdateItem(member_view meber)
        {
            int result = 0;

            string strSql = "update member_table set workcode='{0}',phone='{1}',department='{2}' where id=" + meber.M_ID;
            string strupdate = string.Format(strSql, meber.M_CODE, meber.M_PHONE, meber.M_DEPAT);

            DbManager.BeginTransaction();
            DbInstance idb = DbManager.GetDbInstance();
            try
            {
                object obj = idb.ExecuteNonQueryBySql(strupdate);
                if (null == obj)
                    throw new Exception("修改用户表失败.");

                //strupdate = String.Format("update user_join set are_role=(select id from are_role_join where are={0} and [role]={1}) where member={2}", meber.A_ID, meber.R_ID,meber.M_ID);
                strupdate = string.Format("update user_join set member={0} , are_role={1} where member={2}",
                    meber.M_ID,
                    meber.J_ID,
                    meber.M_ID);

                result = idb.ExecuteNonQueryBySql(strupdate);

                DbManager.Commit();
            }
            catch (Exception ex)
            {
                DbManager.Rollback();
                throw ex;
            }

            return result;

        }

        /// <summary>
        /// 注册
        /// </summary>
        /// <param name="reg"></param>
        /// <returns></returns>
        public int Reguser(RegInfo reg)
        {
            DbCommand cmd = db.GetStoredProcCommand("sp_RegUser");
            db.AddInParameter(cmd, "@name", DbType.String, reg.username);
            db.AddInParameter(cmd, "@sex", DbType.Int32, reg.sex);
            db.AddInParameter(cmd, "@phone", DbType.String, reg.tel);
            db.AddInParameter(cmd, "@department", DbType.String, reg.department);
            db.AddInParameter(cmd, "@workcode", DbType.String, reg.workcode);
            db.AddInParameter(cmd, "@email", DbType.String, reg.email);
            db.AddInParameter(cmd, "@password", DbType.String, reg.pwd.ToMD5());
            db.AddInParameter(cmd, "@areid", DbType.Int32, reg.areid);
            db.AddInParameter(cmd, "@roleid", DbType.Int32, reg.zwid);
            db.AddOutParameter(cmd, "@erro", DbType.Int32, 4);
            db.ExecuteNonQuery(cmd);
            int err = db.GetParameterValue(cmd, "@erro").ToInt(0);
            return err;
        }
        /// <summary>
        /// 获得区域
        /// </summary>
        /// <returns></returns>
        public DataView GetTheAreaList()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select *  from ARE_TABLE ");
            DbCommand cmd = db.GetSqlStringCommand(sb.ToString());
            DataTable dt = db.ExecuteDataTable(cmd);

            return dt.DefaultView;
        }
        /// <summary>
        /// 获得职务
        /// </summary>
        /// <param name="areaId"></param>
        /// <returns></returns>
        public DataView GetTheDepartmentList(int areaId)
        {
            //string strsql = "SELECT ROLE_TABLE.id, ROLE_TABLE.name FROM  ROLE_TABLE JOIN  ARE_ROLE_JOIN  ON  ARE_ROLE_JOIN.ROLE  =  ROLE_TABLE.ID JOIN  ARE_TABLE ON  ARE_ROLE_JOIN.ARE = ARE_TABLE.ID WHERE  ARE_ROLE_JOIN.[status]=1 and ARE_TABLE.ID = " + areaId + " AND  ROLE_TABLE.POWER = 0";
            string strsql = "select J_ID, R_ID, R_NAME from are_role_view where R_POWER=0 AND J_STATUS=1 AND A_ID=" + areaId;

            DbCommand cmd = db.GetSqlStringCommand(strsql);
            DataTable dt = db.ExecuteDataTable(cmd);

            return dt.DefaultView;
        }

        /// <summary>
        /// 获得职务
        /// </summary>
        /// <param name="areaId"></param>
        /// <returns></returns>
        public DataView GetTheDepartmentList(int areaId, int power)
        {
            string strsql = "SELECT ROLE_TABLE.id, ROLE_TABLE.name FROM  ROLE_TABLE JOIN  ARE_ROLE_JOIN  ON  ARE_ROLE_JOIN.ROLE  =  ROLE_TABLE.ID JOIN  ARE_TABLE ON  ARE_ROLE_JOIN.ARE = ARE_TABLE.ID WHERE ARE_ROLE_JOIN.[status]=1 and ARE_TABLE.ID = " + areaId;
            if (power == 0)
                strsql += " AND  ROLE_TABLE.POWER = " + power;
            else
                strsql += " AND  ROLE_TABLE.POWER <1 ";


            DbCommand cmd = db.GetSqlStringCommand(strsql);
            DataTable dt = db.ExecuteDataTable(cmd);

            return dt.DefaultView;
        }

        /// <summary>
        /// 获得所有为审核的用户列表
        /// </summary>
        /// <param name="a_live"></param>
        /// <returns></returns>
        public PageList<object> GetTheRegUserListOne(int a_live, int a_id, PageView pageInfo)
        {

            string strWhere = " WHERE M_STATUS= -1 ";
            if (pageInfo.SearchParams["userName"] != null)
            {
                strWhere += "  and M_NAME like '%" + pageInfo.SearchParams["userName"].Replace("'", "") + "%'";
            }

            if (a_live > 0) strWhere += "  and A_ID=" + a_id;

            PageList<object> list = new PageList<object>();

            string strSql = "SELECT  count(*) as Row FROM  MEMBER_VIEW " + strWhere;
            int rowCount = db.ExecuteScalarBySql(strSql).ToInt(0);


            string orderby = " Order By  A_ID ";
            if (pageInfo.SortName != "")
                orderby = string.Format(" Order By {0} {1}", pageInfo.SortName, pageInfo.SortOrder);
            strSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) AS rows,{1} From {2} {3})As T0 Where  rows Between {4} And {5}";


            strSql = string.Format(strSql, orderby, "*", "MEMBER_VIEW", strWhere, ((pageInfo.PageIndex - 1) * pageInfo.PageSize + 1), pageInfo.PageIndex * pageInfo.PageSize);

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);


            list.Total = rowCount;
            list.Data = dt;

            return list;
        }

        // Execl 下载 - 导出的用户列表.
        public DataTable MemberDownloadExecl(int areid, int roleid, int status, string name)
        {
            string sql = "SELECT ";
            sql += "member_view.M_ID as 编号, ";
            sql += "member_view.M_NAME as 姓名, ";
            sql += "case(member_view.M_SEX) when 0 then '女'  else '男' end as 性别, ";
            sql += "member_view.M_PHONE as 电话, ";
            sql += "member_view.M_DEPAT as 部门, ";
            sql += "member_view.M_CODE as  工号, ";
            sql += "member_view.M_EMAIL as 电子邮件, ";
            sql += "convert(varchar(12),member_view.M_DATE,20) as 创建日期, ";
            sql += "member_view.A_NAME as 所属区域, ";
            sql += "member_view.R_NAME as 隶属职务, ";
            sql += "case(member_view.M_STATUS) when 0 then '启用' else '停用' end as 状态, ";
            sql += "member_view.A_ID, ";
            sql += "member_view.R_ID From member_view WHERE R_POWER=0 AND  J_STATUS = 1 ";
            sql += (areid > -1 ? " AND member_view.A_ID =" + areid : "");
            sql += (roleid > -1 ? " AND member_view.R_ID=" + roleid : "");
            sql += (status == 0 || status == 1 ? " AND member_view.M_STATUS=" + status : "");
            sql += (name.Length > 0 ? " AND member_view.M_NAME like '%" + name + "%'" : "");

            DbCommand cmd = db.GetSqlStringCommand(sql);
            DataTable dt = db.ExecuteDataTable(cmd);

            dt.Columns.Remove("R_ID");
            dt.Columns.Remove("A_ID");

            return dt;
        }


        /// <summary>
        /// 根据区域来 角色来获取
        /// </summary>
        /// <param name="a_live"></param>
        /// <returns></returns>
        public PageList<object> GetTheRegUserList(int areid, int rolid, int status, PageView pageInfo)
        {

            string strWhere = " WHERE R_POWER=0 AND  J_STATUS = 1 ";

            if (areid > 0)
                strWhere += "  and A_ID=" + areid;
            if (rolid > 0)
                strWhere += "  and R_ID=" + rolid;

            if (pageInfo.SearchParams["userName"] != null)
                strWhere += "  and M_NAME like '%" + pageInfo.SearchParams["userName"].Replace("'", "") + "%'";

            if (status == 0 || status == 1)
                strWhere += " and M_STATUS=" + status;


            PageList<object> list = new PageList<object>();

            string strSql = "SELECT  count(*) as Row FROM  MEMBER_VIEW " + strWhere;
            int rowCount = db.ExecuteScalarBySql(strSql).ToInt(0);


            string orderby = " Order By  A_ID ";
            if (pageInfo.SortName != "")
                orderby = string.Format(" Order By {0} {1}", pageInfo.SortName, pageInfo.SortOrder);
            strSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) AS rows,{1} From {2} {3})As T0 Where  rows Between {4} And {5}";


            strSql = string.Format(strSql, orderby, "*", "MEMBER_VIEW", strWhere, ((pageInfo.PageIndex - 1) * pageInfo.PageSize + 1), pageInfo.PageIndex * pageInfo.PageSize);

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);

            list.Total = rowCount;
            list.Data = dt;

            return list;
        }

        /// <summary>
        /// 判断当前是否是洲级管理员
        /// </summary>
        /// <param name="curr"></param>
        /// <returns></returns>
        public int IsSuperAdministator(loginInfo curr)
        { 
            int result = 0;
            if (curr.A_LEVEL == 0 && curr.R_POWER == 1)
                result = 1;
            return result;
        }


        /// <summary>
        /// 审核状态
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateUserStates(int id, int state)
        {
            string strSql = "UPDATE MEMBER_TABLE  SET  MEMBER_TABLE.STATUS = " + state + " where id=" + id;
            DbCommand cmd = db.GetSqlStringCommand(strSql);
            int R = db.ExecuteNonQuery(cmd);

            return R;
        }
        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteUserItem(int id)
        {
            //string strSql = "if  EXISTS(select id from member_table where [status]=-1 and id=" + id + ") begin ";
            //strSql += " delete from  user_join  where  member=" + id + "  delete from  member_table  where  id=" + id + "  end";
            //DbCommand cmd = db.GetSqlStringCommand(strSql);
            //int R = db.ExecuteNonQuery(cmd);

            int result = 0;
            DbManager.BeginTransaction();
            DbInstance myDB = DbManager.GetDbInstance();

            try {
                myDB.ExecuteNonQueryBySql("delete from user_join where member=" + id);
                myDB.ExecuteNonQueryBySql("delete from member_table where id="+id);

                DataTable ctable = myDB.ExecuteDataTableBySql("select id from candidate_table where member="+id);
                if (ctable.Rows.Count > 0) {
                    StringBuilder sb = new StringBuilder();
                    foreach (DataRow row in ctable.Rows) {
                        sb.Append(row["id"].ToString() + ",");
                    }
                    string cids = sb.ToString().Trim(',');

                    StringBuilder scb = new StringBuilder();
                    DataTable stable = myDB.ExecuteDataTableBySql("select id from sheet_table where candidate in(" + cids + ")");
                    if (stable.Rows.Count > 0){
                        foreach (DataRow srow in stable.Rows)
                        {
                            scb.Append(srow["id"].ToString() + ",");
                        }
                        string sids = scb.ToString().Trim(',');
                        myDB.ExecuteNonQueryBySql("delete from reply_table where sheet in (" + sids + ")");
                    }

                    myDB.ExecuteNonQueryBySql("delete from sheet_table where candidate in (" + cids + ")");
                    myDB.ExecuteNonQueryBySql("delete from candidate_table where member =" + id);
                }

                DbManager.Commit();
                result = 1;
            }catch(Exception e) {
                DbManager.Rollback();
            }

            return result;
        }

        /// <summary>
        /// 修改用户
        /// </summary>
        /// <param name="Item"></param>
        /// <returns></returns>
        public int UpdateUserItem(member_view Item)
        {
            string strsql = "update member_table set sex={0},phone='{1}',department='{2}',workcode='{3}',email='{4}',[status]={5} where id={8} ";
            strsql += " update user_join set are_role=(select id from are_role_join where are ={6} and [role] ={7}) where member={8} ";
            strsql = string.Format(strsql, Item.M_SEX, Item.M_PHONE, Item.M_DEPAT, Item.M_CODE, Item.M_EMAIL, Item.M_STATUS, Item.A_ID, Item.R_ID, Item.M_ID);
            DbCommand cmd = db.GetSqlStringCommand(strsql);
            int R = db.ExecuteNonQuery(cmd);

            return R;
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="id"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public int UpdatePwd(int id, string pwd)
        {
            string strSql = "update member_table  set [password]='" + pwd.ToMD5() + "'  where id=" + id;

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            int R = db.ExecuteNonQuery(cmd);

            return R;
        }
        /// <summary>
        /// 升级权
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="aid"></param>
        /// <param name="jid"></param>
        /// <param name="rid"></param>
        /// <returns></returns>
        public int UpUserRole(int userid, int aid, int jid, int rid)
        {
            string strSql = "declare  @jid int  select  @jid=id from are_role_join where are={0} and [role]={1}   UPDATE  user_join set are_role=@jid where member={2}";

            strSql = string.Format(strSql, aid, rid, userid);

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            int R = db.ExecuteNonQuery(cmd);

            return R;
        }

        /// <summary>
        /// 根据 leve 判断是否是洲管
        /// </summary>
        /// <param name="leve"></param>
        /// <returns></returns>
        public DataView GetTheAreaList(int leve, int areid)
        {
            string strsql = "select *  from are_table ";

            if (leve != 0)
            {
                strsql = " select *  from are_table where id=" + areid;
            }

            DbCommand cmd = db.GetSqlStringCommand(strsql);
            DataTable dt = db.ExecuteDataTable(cmd);

            return dt.DefaultView;
        }

        /// <summary>
        /// 根据区域 权限来获得
        /// </summary>
        /// <param name="areid"></param>
        /// <param name="power"></param>
        /// <returns></returns>
        public DataView GetTherAreaPowerRoleList(int areid, int power)
        {
            string strSql = "SELECT  ROLE_TABLE.ID,ROLE_TABLE.NAME FROM  ARE_ROLE_JOIN  JOIN   ROLE_TABLE  ON  ROLE_TABLE.ID  =  ARE_ROLE_JOIN.ROLE WHERE   ARE_ROLE_JOIN.ARE =" + areid + " and ROLE_TABLE.power=" + power;

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);

            return dt.DefaultView;
        }

        // 取得区域-角色关联ID.
        public int GetUserAreRoleID(int areid, int roleid)
        {
            string sql = "SELECT id FROM are_role_join WHERE [status] = 1 and are=" + areid + " AND role=" + roleid;
            return db.ExecuteScalarBySql(sql).ToInt();
        }

    }
}
